﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Wei8808Cms.Model;
using Wei8808Cms.Utility;

namespace Wei8808Cms.DAL
{
    public class ProductDAL : DbHelperBase
    {
        public int GetCount(ProductQcInfo qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM [Product] WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.ProductName))
            {
                strSql.Append(" AND ProductName LIKE '%" + qcmodel.ProductName + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.Status))
            {
                strSql.Append(" AND Status=@Status");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.Status))
                    {
                        Db.AddInParameter(cmd, "@Status", qcmodel.Status);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public DataSet GetPageList(ProductQcInfo qcmodel)
        {
            var ds = new DataSet();
            var strSql = new StringBuilder();
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by OrderId) AS idx,[PId],[ProductName],[ProductTitle],[ProductIcon],[OrderId],[Status],[CreateUser],[CreateTime] FROM [Product] ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.ProductName))
            {
                strSql.Append(" AND ProductName LIKE '%" + qcmodel.ProductName + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.Status))
            {
                strSql.Append(" AND Status=@Status");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.Status))
                    {
                        Db.AddInParameter(cmd, "@Status", qcmodel.Status);
                    }
                    ds = Db.ExecuteDataSet(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return ds;
        }

        public bool Add(ProductInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [Product]([ProductName],[ProductTitle],[ProductContent],[ProductIcon],[OrderId],[Status],[CreateUser],[CreateTime]) ");
            strSql.Append(" VALUES(@ProductName,@ProductTitle,@ProductContent,@ProductIcon,@OrderId,@Status,@CreateUser,@CreateTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@ProductName", model.ProductName);
                    Db.AddInParameter(cmd, "@ProductTitle", model.ProductTitle);
                    Db.AddInParameter(cmd, "@ProductContent", model.ProductContent);
                    Db.AddInParameter(cmd, "@ProductIcon", model.ProductIcon);
                    Db.AddInParameter(cmd, "@OrderId", model.OrderId);
                    Db.AddInParameter(cmd, "@Status", model.Status);
                    Db.AddInParameter(cmd, "@CreateUser", model.CreateUser);
                    Db.AddInParameter(cmd, "@CreateTime", model.CreateTime);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Delete(List<ProductInfo> list)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [Product] WHERE PId=@PId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PId", DbType.Int32);
                    foreach (var i in list)
                    {
                        cmd.Parameters["@PId"].Value = i.PId;
                        Db.ExecuteNonQuery(cmd);
                    }
                    result = true;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public ProductInfo GetProductInfo(int id)
        {
            var model = new ProductInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [PId],[ProductName],[ProductTitle],[ProductIcon],[OrderId],[Status],[ProductContent] FROM [Product] where PId=@PId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PId", id);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            model.PId = Convert.ToInt32(reader["PId"]);
                            model.ProductName = reader["ProductName"] == obj ? null : Convert.ToString(reader["ProductName"]);
                            model.ProductTitle = reader["ProductTitle"] == obj ? null : Convert.ToString(reader["ProductTitle"]);
                            model.ProductIcon = reader["ProductIcon"] == obj ? null : Convert.ToString(reader["ProductIcon"]);
                            model.ProductContent = reader["ProductContent"] == obj ? null : Convert.ToString(reader["ProductContent"]);
                            if (reader["OrderId"] != DBNull.Value)
                                model.OrderId = Convert.ToInt32(reader["OrderId"]);
                            if (reader["Status"] != DBNull.Value)
                                model.Status = Convert.ToInt32(reader["Status"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool Update(ProductInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [Product] SET [ProductName] = @ProductName,[ProductTitle] = @ProductTitle,[ProductContent] = @ProductContent,[ProductIcon] = @ProductIcon,[OrderId] = @OrderId,[Status]=@Status,[ModifyUser] = @ModifyUser,[ModifyTime] = @ModifyTime WHERE PId=@PId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@ProductName", model.ProductName);
                    Db.AddInParameter(cmd, "@ProductTitle", model.ProductTitle);
                    Db.AddInParameter(cmd, "@ProductIcon", model.ProductContent);
                    Db.AddInParameter(cmd, "@ProductContent", model.ProductContent);
                    Db.AddInParameter(cmd, "@OrderId", model.OrderId);
                    Db.AddInParameter(cmd, "@Status", model.Status);
                    Db.AddInParameter(cmd, "@ModifyUser", model.ModifyUser);
                    Db.AddInParameter(cmd, "@ModifyTime", model.ModifyTime);
                    Db.AddInParameter(cmd, "@PId", model.PId);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool UpdateField(int id, string strField)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE Product set " + strField);
            strSql.Append(" where PId=@PId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<ProductInfo> GetProductList()
        {
            var strSql = new StringBuilder();
            var list = new List<ProductInfo>();
            
            object obj = DBNull.Value;
            strSql.Append(" SELECT [PId],[ProductName],[ProductTitle],[ProductContent],[ProductIcon] FROM [Product] where Status=1 ");
            strSql.Append(" ORDER by OrderId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ProductInfo();
                            if (reader["PId"] != DBNull.Value)
                                model.PId = Convert.ToInt32(reader["PId"]);
                            model.ProductName = reader["ProductName"] == obj ? null : Convert.ToString(reader["ProductName"]);
                            model.ProductTitle = reader["ProductTitle"] == obj ? null : Convert.ToString(reader["ProductTitle"]);
                            model.ProductContent = reader["ProductContent"] == obj ? null : Convert.ToString(reader["ProductContent"]);
                            model.ProductIcon = reader["ProductIcon"] == obj ? null : Convert.ToString(reader["ProductIcon"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
